package com.baoyouqun.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baoyouqun.entity.UserVipTime;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface UserVipTimeMapper extends BaseMapper<UserVipTime> {

    /**
     * 批量查询用户的有效VIP（endTime > 当前时间）
     *
     * @param userIds 用户ID列表
     * @return 有效VIP列表
     */
    @Select("""
            <script>
                SELECT id, user_id, vip_level_id, end_time 
                FROM user_vip_time 
                WHERE user_id IN 
                <foreach collection="userIds" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
                AND end_time > NOW()
                ORDER BY end_time DESC
            </script>
            """)
    List<UserVipTime> selectBatchValidByUserIds(List<String> userIds);


    @Select("SELECT " +
            "  u.city_code AS cityCode,  " +  // 与用户表关联获取地区编码
            "  COUNT(DISTINCT uvt.user_id) AS count  " +  // 去重统计VIP用户数
            "FROM user_vip_time uvt " +
            "INNER JOIN user u ON uvt.user_id = u.id " +  // 关联用户表获取地区信息
            "WHERE uvt.end_time > NOW() " +  // VIP有效期未结束
            "  AND uvt.start_time <= NOW() " +  // VIP已开始生效
            "GROUP BY u.city_code")
        // 按地区编码分组
    List<Map<String, Object>> countVipByAreaCodes();

    /**
     * 批量查询多个县市编码的有效VIP数总和（用于省份汇总）
     *
     * @param districtCodes 县市编码列表
     * @return 总VIP数
     */
    @Select("""
                <script>
                SELECT COUNT(DISTINCT uvt.user_id) AS totalVipCount
                FROM user_vip_time uvt 
                INNER JOIN user u ON uvt.user_id = u.id 
                WHERE uvt.end_time &gt; NOW() AND uvt.start_time &lt;= NOW() 
                AND u.back_worker = 0 AND u.logout = 0 AND u.dis_enable = 0 
                AND u.city_code IN 
                <foreach collection="districtCodes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                </script>
            """)
    Long selectTotalVipCountByDistrictCodes(@Param("districtCodes") List<String> districtCodes);

    /**
     * 批量查询单个县市编码的有效VIP数（用于县市单独统计）
     *
     * @param districtCodes 县市编码列表
     * @return key:县市编码, value:VIP数
     */
    @Select("""
                <script>
                SELECT u.city_code AS areaCode, COUNT(DISTINCT uvt.user_id) AS vipCount 
                FROM user_vip_time uvt 
                INNER JOIN user u ON uvt.user_id = u.id 
                WHERE uvt.end_time &gt; NOW() AND uvt.start_time &lt;= NOW() 
                AND u.back_worker = 0 AND u.logout = 0 AND u.dis_enable = 0 
                AND u.city_code IN 
                <foreach collection="districtCodes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                GROUP BY u.city_code
                </script>
            """)
    Map<String, Long> batchSelectVipCountByDistrictCodes(@Param("districtCodes") List<String> districtCodes);


    /**
     * 统计有效VIP总数（用于校验）
     */
    @Select("""
             <script>
                 SELECT COUNT(DISTINCT uvt.user_id)
                 FROM user_vip_time uvt 
                 INNER JOIN user u ON uvt.user_id = u.id 
                 WHERE uvt.end_time &gt; NOW() AND uvt.start_time &lt;= NOW() 
                 AND u.back_worker = 0 AND u.logout = 0 AND u.dis_enable = 0 
                 <if test='provinceCode != null and provinceCode != ""'>AND u.province = #{provinceCode}</if> 
                 <if test='cityCode != null and cityCode != ""'>AND u.city = #{cityCode}</if>
            </script>
             """)
    Long selectVipTotalCount(
            @Param("provinceCode") String provinceCode,
            @Param("cityCode") String cityCode
    );
}